﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using System.Globalization;
using Player.MyAttribute;
using Player.DataTable;
using System.Reflection;

namespace storeProcedure
{
    public class SessionProperty
    {
        public static readonly DateTime UNKNOWN_SESSION_DATE_TIME = SqlDateTime.MinValue.Value;
    }

    public enum Direction
    {
        Input,
        Output
    }

    public enum WashEventType
    {
        Create = 1,
        Destroy = 2,
        Activate = 3,
        Deactivate = 4,
        RectangleChange = 5,
        Minimize = 6,
        Maximize = 7,
        Restore = 8,
        Settext = 9,
        Clicktext = 10,
        ConsoleText = 11,
        WindowActivate = 12,
    }

    public enum PacketType
    {
        Unknown = 0,
        ResizeWindow = 1,
        StandardIn = 2,
        StandardOut = 3,
        StandardError = 4,
    }

    /// <summary>
    /// Unix => v1
    /// WindowsAndUnix => v2
    /// </summary>
    public enum AuditStoreDatabaseType
    {
        WindowsAndUnix = 1,
        Unix = 2
    }

    #region windows

    public class WindowsSessionGetHeader
    {
        public enum Variables : int
        {
            [Variable(Direction.Input)]
            SessionId,
            [Variable(Direction.Input)]
            AuditDatabaseId,
            [Variable(Direction.Output)]
            PixelFormat,
            [Variable(Direction.Output)]
            Palette,
            [Variable(Direction.Output)]
            User,
            [Variable(Direction.Output)]
            StartTime,
            [Variable(Direction.Output)]
            TimeStampOffSet,
            [Variable(Direction.Output)]
            Machine
        }

        public struct Input
        {
            [InputMapping((int)Variables.SessionId)]
            public Guid SessionId;

            [InputMapping((int)Variables.AuditDatabaseId)]
            public int AuditDatabaseId;

            public Input(Guid sessionId, int auditDatabaseId)
            {
                AuditDatabaseId = auditDatabaseId;
                SessionId = sessionId;
            }
        }

        public struct Output
        {
            [OutputMapping((int)Variables.PixelFormat, SqlDbType.TinyInt)]
            public short PixelFormat;
            [OutputMapping((int)Variables.Palette, SqlDbType.Binary, 2048)]
            public byte[] Palette;
            [OutputMapping((int)Variables.User, SqlDbType.NVarChar, 511)]
            public string User;
            [OutputMapping((int)Variables.StartTime, SqlDbType.DateTime)]
            public DateTime StartTime;
            [OutputMapping((int)Variables.TimeStampOffSet, SqlDbType.BigInt)]
            public long TimeStampOffset;
            [OutputMapping((int)Variables.Machine, SqlDbType.NVarChar, 255)]
            public string Machine;
        }

        public static Output ExecuteNonQuery(string connectionstring, Input input)
        {
            Output output = new Output();
            ExecuteNonQuery(input.SessionId, input.AuditDatabaseId, out output.PixelFormat, out output.Palette, out output.User, out output.StartTime, out output.TimeStampOffset, out output.Machine);
            return output;
        }

        public static Output ExecuteNonQuery(Guid sessionId, int auditDatabaseId, out short pixelFormat, out byte[] palette, out string user, out DateTime startTime, out long timestampOffset, out string machine)
        {

            pixelFormat = 0;
            palette = null;
            user = string.Empty;
            startTime = SqlDateTime.MinValue.Value;
            timestampOffset = 0;
            machine = string.Empty;

            string sql = SqlHelper.Build("SELECT", Variables.PixelFormat, "=", WashSession.PixelFormat, ",",
                                         Variables.Palette, "=", WashSession.ColorMap, ",",
                                         Variables.User, "=", Session.UserName, ",",
                                         Variables.StartTime, "=", Session.StartTime, ",",
                                         Variables.TimeStampOffSet, "=", Session.TimeStampOffset, ",",
                                         Variables.Machine, "=", Session.MachineName,
                               "FROM", typeof(WashSession), "WITH (NOLOCK)", "INNER JOIN", typeof(Session),
                               "WITH (NOLOCK)", "ON", WashSession.SessionId, "=", Session.SessionId,
                               "WHERE", WashSession.SessionId, "=", Variables.SessionId
                               );
            try
            {
                string connectionString = Player.App.ConnectionString;
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@SessionId", sessionId);

                        SqlParameter p = new SqlParameter("@PixelFormat", SqlDbType.SmallInt);
                        p.Direction = ParameterDirection.Output;
                        command.Parameters.Add(p);

                        p = new SqlParameter("@Palette", SqlDbType.VarBinary, 2048);
                        p.Direction = ParameterDirection.Output;
                        command.Parameters.Add(p);

                        p = new SqlParameter("@User", SqlDbType.NVarChar, 511);
                        p.Direction = ParameterDirection.Output;
                        command.Parameters.Add(p);

                        p = new SqlParameter("@StartTime", SqlDbType.DateTime);
                        p.Direction = ParameterDirection.Output;
                        command.Parameters.Add(p);

                        p = new SqlParameter("@TimeStampOffSet", SqlDbType.BigInt);
                        p.Direction = ParameterDirection.Output;
                        command.Parameters.Add(p);

                        p = new SqlParameter("@Machine", SqlDbType.NVarChar, 255);
                        p.Direction = ParameterDirection.Output;
                        command.Parameters.Add(p);

                        command.ExecuteNonQuery();

                        command.GetValue(Variables.PixelFormat, ref pixelFormat);
                        command.GetValue(Variables.Palette, ref palette);
                        command.GetValue(Variables.User, ref user);
                        command.GetValue(Variables.StartTime, ref startTime);
                        command.GetValue(Variables.TimeStampOffSet, ref timestampOffset);
                        command.GetValue(Variables.Machine, ref machine);
                    }
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            return new Output();
        }
    }

    public class WindowsSessionGetEvents
    {

        public enum Variables : int
        {
            [Variable(Direction.Input)]
            SessionId,

            [Variable(Direction.Input)]
            AuditDatabaseId,
        }

        public struct Input
        {
            [InputMapping((int)Variables.SessionId)]
            public Guid SessionId;

            [InputMapping((int)Variables.AuditDatabaseId)]
            public int AuditDatabaseId;

            public Input(Guid sessionId, int auditDatabaseId)
            {
                AuditDatabaseId = auditDatabaseId;
                SessionId = sessionId;
            }
        }

        public struct Output
        {
            [ColumnMapping(typeof(WashEvent), (int)WashEvent.SessionId)]
            public Guid SessionId;
            [ColumnMapping(typeof(WashEvent), (int)WashEvent.SequenceNumber)]
            public int SequenceNumber;
            [ColumnMapping(typeof(WashEvent), (int)WashEvent.SystemTime)]
            public long SystemTime;
            [ColumnMapping(typeof(WashEvent), (int)WashEvent.TimeStamp)]
            public long TimeStamp;
            [ColumnMapping(typeof(WashEvent), (int)WashEvent.Event)]
            public WashEventType Event;
            [ColumnMapping(typeof(WashEvent), (int)WashEvent.Pid)]
            public int Pid;
            [ColumnMapping(typeof(WashEvent), (int)WashEvent.WindowId)]
            public long WindowId;
            [ColumnMapping(typeof(WashEvent), (int)WashEvent.Module)]
            public string Module;
            [ColumnMapping(typeof(WashEvent), (int)WashEvent.Text)]
            public string Text;
            [ColumnMapping(typeof(WashEvent), (int)WashEvent.X)]
            public int X;
            [ColumnMapping(typeof(WashEvent), (int)WashEvent.Y)]
            public int Y;
            [ColumnMapping(typeof(WashEvent), (int)WashEvent.Width)]
            public int Width;
            [ColumnMapping(typeof(WashEvent), (int)WashEvent.Height)]
            public int Height;
        }
        public static IEnumerable<Output> Execute(string connectstring, Input input)
        {
            return null;
        }

    }

    public class WindowsSessionGetFirstFrameSet
    {
        public enum Variables : int
        {
            [Variable(Direction.Input)]
            SessionId,

            [Variable(Direction.Input)]
            AuditDatabaseId
        }

        public struct Input
        {
            [InputMapping((int)Variables.SessionId)]
            public Guid SessionId;

            [InputMapping((int)Variables.AuditDatabaseId)]
            public int AuditDatabaseId;

            public Input(Guid sessionId, int auditDatabaseId)
            {
                SessionId = sessionId;
                AuditDatabaseId = auditDatabaseId;
            }
        }

        public struct Output
        {
            [ColumnMapping(typeof(WashData), (int)WashData.SystemTime)]
            public long SystemTime;
            [ColumnMapping(typeof(WashData), (int)WashData.TimeStamp)]
            public long TimeStamp;
            [ColumnMapping(typeof(WashData), (int)WashData.Data)]
            public SqlBytes Data;
        }

        public static IEnumerable<Output> Execute(string connectionstring, Input input, CommandBehavior behavior)
        {
            string sql = SqlHelper.Build("SELECT TOP(1)", SqlHelper.List(WashData.SystemTime, WashData.TimeStamp, WashData.Data),
                              "FROM", typeof(WashData), "WITH (NOLOCK)", "WHERE", WashData.SessionId, "=", Variables.SessionId,
                              "ORDER BY", WashData.SequenceNumber, "ASC");

            SqlDataReader reader = null;

            string connectionString = connectionstring;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("SessionId", Player.App.SessionId);
                    reader = command.ExecuteReader();
                    try
                    {
                        while (reader.Read())
                        {
                            Output output = new Output();
                            output.SystemTime = long.Parse(reader.GetValue(0).ToString());
                            output.TimeStamp = long.Parse(reader.GetValue(1).ToString());
                            output.Data = reader.GetSqlBytes(2);
                            yield return output;
                        }
                    }
                    finally
                    {
                        if (reader != null)
                            reader.Dispose();
                    }
                }
            }
        }
    }

    public class WindowsSessionGetFrameSets
    {
        public enum Variables : int
        {
            [Variable(Direction.Input)]
            SessionId,
            [Variable(Direction.Input)]
            AuditDatabaseId,
            [Variable(Direction.Input)]
            StartFrom,
        }

        public struct Input
        {
            [InputMapping((int)Variables.SessionId)]
            public Guid SessionId;

            [InputMapping((int)Variables.AuditDatabaseId)]
            public int AuditDatabaseId;

            [InputMapping((int)Variables.StartFrom)]
            public long StartFrom;

            public Input(Guid sessionId, int auditDatabaseId, long startFrom)
            {
                SessionId = sessionId;
                AuditDatabaseId = auditDatabaseId;
                StartFrom = startFrom;
            }
        }

        public struct Output
        {
            [ColumnMapping(typeof(WashData), (int)WashData.SystemTime)]
            public long SystemTime;
            [ColumnMapping(typeof(WashData), (int)WashData.TimeStamp)]
            public long TimeStamp;
            [ColumnMapping(typeof(WashData), (int)WashData.Data)]
            public SqlBytes Data;
        }

        public static IEnumerable<Output> Execute(string connectionstring, Input input, CommandBehavior behavior)
        {
            string sql = SqlHelper.Build(
                    "DECLARE @time BIGINT;",
                    "SELECT TOP(1)", "@time=", WashData.TimeStamp, "FROM", typeof(WashData), "WITH (NOLOCK)",
                    "WHERE", WashData.SessionId, "=", Variables.SessionId,
                    "AND", WashData.TimeStamp, "<", Variables.StartFrom,
                    "ORDER BY", WashData.TimeStamp, "DESC;",

                    "SET @time = ISNULL(@time,", Variables.StartFrom, ");",

                    "SELECT", SqlHelper.List(WashData.SystemTime, WashData.TimeStamp, WashData.Data), "FROM", typeof(WashData), "WITH (NOLOCK)",
                    "WHERE", "(", WashData.SessionId, "=", Variables.SessionId, "AND", WashData.TimeStamp, ">=@time)",
                    "ORDER BY", WashData.TimeStamp, "ASC");

            string connectionString = connectionstring;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("SessionId", input.SessionId);
                    command.Parameters.AddWithValue("StartFrom", input.StartFrom);

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Output output = new Output();
                            output.SystemTime = long.Parse(reader.GetValue(0).ToString());
                            output.TimeStamp = long.Parse(reader.GetValue(1).ToString());
                            output.Data = reader.GetSqlBytes(2);
                            yield return output;
                        }
                    }
                }
            }
        }
    }

    public class WindowsSessionGetLastFrameSet
    {
        public enum Variables : int
        {
            [Variable(Direction.Input)]
            SessionId,

            [Variable(Direction.Input)]
            AuditDatabaseId
        }

        public struct Input
        {
            [InputMapping((int)Variables.SessionId)]
            public Guid SessionId;

            [InputMapping((int)Variables.AuditDatabaseId)]
            public int AuditDatabaseId;

            public Input(Guid sessionId, int auditDatabaseId)
            {
                SessionId = sessionId;
                AuditDatabaseId = auditDatabaseId;
            }
        }

        public struct Output
        {
            [ColumnMapping(typeof(WashData), (int)WashData.SystemTime)]
            public long SystemTime;
            [ColumnMapping(typeof(WashData), (int)WashData.TimeStamp)]
            public long TimeStamp;
            [ColumnMapping(typeof(WashData), (int)WashData.Data)]
            public SqlBytes Data;
        }

        public static IEnumerable<Output> Execute(string connectionstring, Input input, CommandBehavior behavior)
        {
            string sql = SqlHelper.Build("SELECT TOP(1)", SqlHelper.List(WashData.SystemTime, WashData.TimeStamp, WashData.Data),
                                "FROM", typeof(WashData), "WITH (NOLOCK)", "WHERE", WashData.SessionId, "=", Variables.SessionId,
                                "ORDER BY", WashData.SequenceNumber, "DESC");

            string connectionString = connectionstring;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("SessionId", input.SessionId);

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Output output = new Output();
                            output.SystemTime = long.Parse(reader.GetValue(0).ToString());
                            output.TimeStamp = long.Parse(reader.GetValue(1).ToString());
                            output.Data = reader.GetSqlBytes(2);
                            yield return output;
                        }
                    }
                }
            }

        }

    }

    #endregion

    #region unix
    public class UnixSessionGetHeader
    {
        public enum Variables : int
        {
            [Variable(Direction.Input)]
            SessionId,
            [Variable(Direction.Input)]
            AuditDatabaseId,
            [Variable(Direction.Output)]
            User,
            [Variable(Direction.Output)]
            Machine,
            [Variable(Direction.Output)]
            StartTime,
            [Variable(Direction.Output)]
            EndTime
        }

        public struct Input
        {
            [InputMapping((int)Variables.SessionId)]
            public Guid SessionId;

            [InputMapping((int)Variables.AuditDatabaseId)]
            public int AuditDatabaseId;

            public Input(Guid sessionId, int auditDatabaseId)
            {
                SessionId = sessionId;
                AuditDatabaseId = auditDatabaseId;
            }
        }

        public struct Output
        {
            [OutputMapping((int)Variables.User, SqlDbType.NVarChar, 255)]
            public string User;
            [OutputMapping((int)Variables.Machine, SqlDbType.NVarChar, 255)]
            public string Machine;
            [OutputMapping((int)Variables.StartTime, SqlDbType.DateTime)]
            public DateTime StartTime;
            [OutputMapping((int)Variables.EndTime, SqlDbType.DateTime)]
            public DateTime? EndTime;
        }

        public static Output ExecuteNonQuery(string connectionString, Input input)
        {
            string username = string.Empty;
            string machineName = string.Empty;
            DateTime startTime = DateTime.MinValue;
            SqlDateTime endTime = SqlDateTime.Null;

            string sql = SqlHelper.Build(//"SET", Variables.EndTime, "= ((0))",
                                "SELECT", Variables.User, "=", USession.UserName, ",",
                                          Variables.Machine, "=", USession.MachineName, ",",
                                          Variables.StartTime, "=", USession.StartTime, ",",
                                          Variables.EndTime, "=", USession.EndTime,
                                "FROM", typeof(USession), "WITH (NOLOCK)",
                                "WHERE", "SessionId=", Variables.SessionId);

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@SessionId", input.SessionId);

                    SqlParameter p = new SqlParameter("@User", SqlDbType.NVarChar, 50);
                    p.Direction = ParameterDirection.Output;
                    command.Parameters.Add(p);

                    p = new SqlParameter("@Machine", SqlDbType.NVarChar, 255);
                    p.Direction = ParameterDirection.Output;
                    command.Parameters.Add(p);

                    p = new SqlParameter("@StartTime", SqlDbType.DateTime);
                    p.Direction = ParameterDirection.Output;
                    command.Parameters.Add(p);

                    p = new SqlParameter("@EndTime", SqlDbType.DateTime);
                    p.Direction = ParameterDirection.Output;
                    command.Parameters.Add(p);

                    command.ExecuteNonQuery();
                    command.GetValue(Variables.User, ref username);
                    command.GetValue(Variables.Machine, ref machineName);
                    command.GetValue(Variables.StartTime, ref startTime);
                    command.GetValue(Variables.EndTime, ref endTime);
                }
            }
            Output output = new Output();
            output.EndTime = endTime.Value;
            output.User = username;
            output.Machine = machineName;
            output.StartTime = startTime;
            return output;
        }
    }

    public class UnixSessionGetFirstFrame
    {
        public enum Variables : int
        {
            [Variable(Direction.Input)]
            Session_Id,
            [Variable(Direction.Input)]
            AuditDatabaseId,
        }

        public struct Input
        {
            [InputMapping((int)Variables.Session_Id)]
            public Guid SessionId;

            [InputMapping((int)Variables.AuditDatabaseId)]
            public int AuditDatabaseId;

            public Input(Guid sessionId, int auditDatabaseId)
            {
                SessionId = sessionId;
                AuditDatabaseId = auditDatabaseId;
            }
        }

        public struct Output
        {
            [ColumnMapping(typeof(RawData), (int)RawData.CapturedTime)]
            public DateTime CapturedTime;
            [ColumnMapping(typeof(RawData), (int)RawData.TimeStamp)]
            public long TimeStamp;
            [ColumnMapping(typeof(RawData), (int)RawData.Type)]
            public PacketType Type;
            [ColumnMapping(typeof(RawData), (int)RawData.TerminalData)]
            public byte[] Data;
            [ColumnMapping(typeof(RawData), (int)RawData.Sequence)]
            public int Sequence;
            [ColumnMapping(typeof(RawData), (int)RawData.ScreenHeight)]
            public int ScreenHeight;
            [ColumnMapping(typeof(RawData), (int)RawData.ScreenWidth)]
            public int ScreenWidth;
        }

        public static IEnumerable<Output> Execute(string conn, Input input, CommandBehavior behavir)
        {
            string connectionString = Player.App.ConnectionString;
            AuditStoreDatabaseType type = AuditStoreDatabaseType.WindowsAndUnix;

            string sql = string.Empty;
            if (type == AuditStoreDatabaseType.WindowsAndUnix)
            {
                //Handle the "Unknown" session which start datetime is SqlDateTime.MinValue(1753/01/01).
                //Since the default replay mode is real time mode, session player will start playing from 1/1/1753 and the user needs to wait in front
                //of the computer for more then 250 years to see the real data.
                //
                //So here need filter the unknown's captured time, make sure we get the real start time of session
                sql = SqlHelper.Build(
                    "SELECT TOP(1) A.* FROM",
                    "(",
                    "SELECT", Snapshot.CapturedTime, ",", Snapshot.TimeStamp, ",", 0, "AS [TYPE],", Snapshot.TerminalData, ",",
                    Snapshot.Sequence, "AS [Sequence], 0 AS [ScreenHeight], 0 AS [ScreenWidth]",
                    "FROM", typeof(Snapshot), "WITH (NOLOCK)",
                    "WHERE", Snapshot.SessionId, "=", Variables.Session_Id,
                    "AND", Snapshot.TimeStamp, ">", SessionProperty.UNKNOWN_SESSION_DATE_TIME.Ticks,
                    "UNION ALL",
                    "SELECT",
                    SqlHelper.List(RawData.CapturedTime, RawData.TimeStamp, RawData.Type, RawData.TerminalData, RawData.Sequence,
                         RawData.ScreenHeight, RawData.ScreenWidth),
                    "FROM", typeof(RawData), "WITH (NOLOCK)",
                    "WHERE", RawData.SessionId, "=", Variables.Session_Id,
                    "AND", RawData.TimeStamp, ">", SessionProperty.UNKNOWN_SESSION_DATE_TIME.Ticks,
                    ") AS A",
                    "ORDER BY [Sequence] ASC, [Type] DESC");
            }
            else if (type == AuditStoreDatabaseType.Unix)
            {
                sql = SqlHelper.Build(
                    "SELECT TOP(1)",
                    URawData.CapturedTime, ",",
                    SqlHelper.DateTimeToBigInt(URawData.CapturedTime), "AS [TimeStamp],",
                   SqlHelper.List(URawData.Type, URawData.TerminalData, URawData.Sequence, URawData.ScreenHeight, URawData.ScreenWidth),
                    "FROM", typeof(URawData), "WITH (NOLOCK)",
                    "WHERE", URawData.Session_Id, "=", Variables.Session_Id,
                    "AND", URawData.CapturedTime, ">", SqlHelper.BigIntToDateTime(SessionProperty.UNKNOWN_SESSION_DATE_TIME.Ticks),
                    "ORDER BY", URawData.Sequence, "ASC");
            }
            else
            {
                throw new Exception("Unknown Audit Store type");
            }

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    //command.Parameters.AddRange(Parameters(new Input(sessionId, auditDatabaseId)));
                    command.Parameters.AddWithValue("@Session_Id", input.SessionId);
                    SqlDataReader reader;
                    try
                    {
                        // Override command timeout
                        //if (commandTimeout >= 0)
                        //{
                        //    command.CommandTimeout = commandTimeout;
                        //}

                        reader = command.ExecuteReader(behavir);
                    }
                    catch (SqlException e)
                    {
                        //connection.Trace("Error - {0}", e.ToString());
                        throw;
                    }

                    int count = 0;
                    try
                    {
                        do
                        {
                            while (reader.Read())
                            {
                                count++;
                                //yield return CreateColumnOutput(reader);
                                yield return new Output()
                                {
                                    CapturedTime = reader.GetDateTime(0),
                                    TimeStamp = reader.GetInt64(1),
                                    Type = (PacketType)reader.GetInt32(2),

                                    //Data =reader.GetBytes(
                                };
                            }
                        } while (reader.NextResult());
                    }
                    finally
                    {
                        command.Cancel();
                        reader.Close();
                    }

                    //connection.Trace("Number of rows returned: {0}", count);

                    // After reading all the data from DataReader, return the data for output parameters
                    List<object> ret = new List<object>();
                    foreach (SqlParameter parameter in command.Parameters)
                    {
                        if (parameter.Direction == ParameterDirection.Output)
                        {
                            ret.Add(parameter.Value);
                            //connection.Trace("Return {0} = {1}", parameter.ParameterName, ToSqlString(parameter.Value));
                        }
                    }

                    if (ret.Count > 0)
                        yield return new Output()
                                  {
                                      CapturedTime = reader.GetDateTime(0),
                                      TimeStamp = reader.GetInt64(1),
                                      Type = (PacketType)reader.GetInt32(2),

                                      //Data =reader.GetBytes(
                                  };
                }
            }
        }
    }

    public class UnixSessionGetLastFrame
    {
        public enum Variables : int
        {
            [Variable(Direction.Input)]
            Session_Id,
            [Variable(Direction.Input)]
            AuditDatabaseId,
        }

        public struct Input
        {
            [InputMapping((int)Variables.Session_Id)]
            public Guid SessionId;

            [InputMapping((int)Variables.AuditDatabaseId)]
            public int AuditDatabaseId;

            public Input(Guid sessionId, int auditDatabaseId)
            {
                SessionId = sessionId;
                AuditDatabaseId = auditDatabaseId;
            }
        }

        public struct Output
        {
            [ColumnMapping(typeof(RawData), (int)RawData.CapturedTime)]
            public DateTime CapturedTime;
            [ColumnMapping(typeof(RawData), (int)RawData.TimeStamp)]
            public long TimeStamp;
            [ColumnMapping(typeof(RawData), (int)RawData.Type)]
            public PacketType Type;
            [ColumnMapping(typeof(RawData), (int)RawData.TerminalData)]
            public byte[] Data;
            [ColumnMapping(typeof(RawData), (int)RawData.Sequence)]
            public int Sequence;
            [ColumnMapping(typeof(RawData), (int)RawData.ScreenHeight)]
            public int ScreenHeight;
            [ColumnMapping(typeof(RawData), (int)RawData.ScreenWidth)]
            public int ScreenWidth;
        }

        public static IEnumerable<Output> Execute(string conn, Input input, CommandBehavior behavir)
        {

            string connectionString = Player.App.ConnectionString;
            AuditStoreDatabaseType type = AuditStoreDatabaseType.WindowsAndUnix;

            string sql = string.Empty;

            if (type == AuditStoreDatabaseType.WindowsAndUnix)
            {
                sql = SqlHelper.Build(
                    "SELECT TOP(1)",
                 SqlHelper.List(RawData.CapturedTime, RawData.TimeStamp, RawData.Type, RawData.TerminalData, RawData.Sequence,
                         RawData.ScreenHeight, RawData.ScreenWidth),
                    "FROM", typeof(RawData), "WITH (NOLOCK)",
                    "WHERE", RawData.SessionId, "=", Variables.Session_Id,
                    "ORDER BY", RawData.Sequence, "DESC");

            }
            else if (type == AuditStoreDatabaseType.Unix)
            {
                sql = SqlHelper.Build(
                    "SELECT TOP(1)",
                    URawData.CapturedTime, ",",
                    SqlHelper.DateTimeToBigInt(URawData.CapturedTime), "AS [TimeStamp],",
                    SqlHelper.List(URawData.Type, URawData.TerminalData, URawData.Sequence, URawData.ScreenHeight, URawData.ScreenWidth),
                    "FROM", typeof(URawData), "WITH (NOLOCK)",
                    "WHERE", URawData.Session_Id, "=", Variables.Session_Id,
                    "ORDER BY", URawData.Sequence, "DESC");
            }
            else
            {
                throw new Exception("Unknown Audit Store type");
            }

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@Session_Id", input.SessionId);

                    SqlDataReader reader;
                    try
                    {
                        // Override command timeout
                        //if (commandTimeout >= 0)
                        //{
                        //    command.CommandTimeout = commandTimeout;
                        //}

                        reader = command.ExecuteReader(behavir);
                    }
                    catch (SqlException e)
                    {
                        //connection.Trace("Error - {0}", e.ToString());
                        throw;
                    }

                    int count = 0;
                    try
                    {
                        do
                        {
                            while (reader.Read())
                            {
                                count++;
                                //yield return CreateColumnOutput(reader);

                                Output output2 = new Output();
                                output2.CapturedTime = reader.GetDateTime(0);
                                output2.TimeStamp = reader.GetInt64(1);
                                output2.Type = (PacketType)reader.GetByte(2);
                                output2.Data = reader.GetSqlBytes(3).Buffer;
                                yield return output2;
                            }
                        } while (reader.NextResult());
                    }
                    finally
                    {
                        command.Cancel();
                        reader.Close();
                    }

                    //connection.Trace("Number of rows returned: {0}", count);

                    // After reading all the data from DataReader, return the data for output parameters
                    List<object> ret = new List<object>();
                    foreach (SqlParameter parameter in command.Parameters)
                    {
                        if (parameter.Direction == ParameterDirection.Output)
                        {
                            ret.Add(parameter.Value);
                            //connection.Trace("Return {0} = {1}", parameter.ParameterName, ToSqlString(parameter.Value));
                        }
                    }

                    if (ret.Count > 0)
                        yield return new Output()
                                  {
                                      CapturedTime = reader.GetDateTime(0),
                                      TimeStamp = reader.GetInt64(1),
                                      Type = (PacketType)reader.GetByte(2),

                                      //Data =reader.GetBytes(
                                  };
                }
            }
        }

    }

    public class UnixSessionGetSnapshot
    {
        public enum Variables : int
        {
            [Variable(Direction.Input)]
            Session_Id,
            [Variable(Direction.Input)]
            AuditDatabaseId,
            [Variable(Direction.Input)]
            TargetTimeStamp
        }

        public struct Input
        {
            [InputMapping((int)Variables.Session_Id)]
            public Guid SessionId;

            [InputMapping((int)Variables.AuditDatabaseId)]
            public int AuditDatabaseId;

            [InputMapping((int)Variables.TargetTimeStamp)]
            public long TargetTimeStamp;

            public Input(Guid sessionId, int auditDatabaseId, long targetTimeStamp)
            {
                SessionId = sessionId;
                AuditDatabaseId = auditDatabaseId;
                TargetTimeStamp = targetTimeStamp;
            }
        }

        public struct Output
        {
            [ColumnMapping(typeof(RawData), (int)RawData.Sequence)]
            public int Sequence;
            [ColumnMapping(typeof(RawData), (int)RawData.CapturedTime)]
            public DateTime CapturedTime;
            [ColumnMapping(typeof(RawData), (int)RawData.TimeStamp)]
            public long TimeStamp;
            [ColumnMapping(typeof(RawData), (int)RawData.TerminalData)]
            public byte[] Snapshot;
        }

        public static IEnumerable<Output> Execute(string connectionstring, Input input)
        {
            //if (!CanReplayAuditTrail(sessionId, auditDatabaseId))
            //{
            //    RaiseUnauthorizedException("You have no permission to replay this Unix session");
            //}


            string connectionString = connectionstring;
            AuditStoreDatabaseType auditDbType = AuditStoreDatabaseType.WindowsAndUnix;

            if (auditDbType == AuditStoreDatabaseType.WindowsAndUnix)
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();

                    string sql = SqlHelper.Build(
                    "SELECT TOP(1)",
                    SqlHelper.List(Snapshot.Sequence, Snapshot.CapturedTime, Snapshot.TimeStamp, Snapshot.TerminalData),
                    "FROM", typeof(Snapshot), "WITH (NOLOCK)",
                    "WHERE", Snapshot.SessionId, "= ", Variables.Session_Id,
                    "AND", Snapshot.TimeStamp, "<=", Variables.TargetTimeStamp,
                    "ORDER BY", Snapshot.Sequence, "DESC");

                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@Session_Id", input.SessionId);
                        command.Parameters.AddWithValue("@TargetTimeStamp", input.TargetTimeStamp);
                        //command.Parameters.AddRange(Parameters(new Input(sessionId, auditDatabaseId, targetTimeStamp)));
                        //Execute(command); 

                        SqlDataReader reader;
                        try
                        {
                            // Override command timeout
                            //if (commandTimeout >= 0)
                            //{
                            //    command.CommandTimeout = commandTimeout;
                            //}

                            reader = command.ExecuteReader();
                        }
                        catch (SqlException e)
                        {
                            //connection.Trace("Error - {0}", e.ToString());
                            throw;
                        }

                        int count = 0;
                        try
                        {
                            do
                            {
                                while (reader.Read())
                                {
                                    count++;
                                    //yield return CreateColumnOutput(reader);

                                    Output output2 = new Output();
                                    output2.Sequence = reader.GetInt32(0);
                                    output2.CapturedTime = reader.GetDateTime(1);
                                    output2.TimeStamp = reader.GetInt64(2);

                                    //output2.Type = (PacketType)reader.GetByte(2);
                                    //output2.Data = reader.GetSqlBytes(3).Buffer;
                                    yield return output2;
                                }
                            } while (reader.NextResult());
                        }
                        finally
                        {
                            command.Cancel();
                            reader.Close();
                        }

                        //connection.Trace("Number of rows returned: {0}", count);

                        // After reading all the data from DataReader, return the data for output parameters
                        //List<object> ret = new List<object>();
                        //foreach (SqlParameter parameter in command.Parameters)
                        //{
                        //    if (parameter.Direction == ParameterDirection.Output)
                        //    {
                        //        ret.Add(parameter.Value);
                        //        //connection.Trace("Return {0} = {1}", parameter.ParameterName, ToSqlString(parameter.Value));
                        //    }
                        //}

                        //if (ret.Count > 0)
                        //    yield return new Output()
                        //    {
                        //        CapturedTime = reader.GetDateTime(0),
                        //        TimeStamp = reader.GetInt64(1),
                        //        Type = (PacketType)reader.GetInt32(2),

                        //        //Data =reader.GetBytes(
                        //    };
                    }
                }
            }
            else if (auditDbType == AuditStoreDatabaseType.Unix)
            {
                //using (SqlConnection connection = new SqlConnection(connectionString))
                //{
                //    connection.Open();
                //    string sql = SqlHelper.Build(
                //    "SELECT TOP(1)",
                //    URawData.Sequence, ",",
                //    URawData.CapturedTime, ",",
                //     SqlHelper.DateTimeToBigInt(URawData.CapturedTime), ",",
                //    URawData.TerminalData,
                //    "FROM", typeof(URawData), "WITH (NOLOCK)",
                //    "WHERE", URawData.Session_Id, "=", Variables.Session_Id,
                //    "AND", URawData.CapturedTime, "<=", SqlHelper.BigIntToDateTime(Variables.TargetTimeStamp),
                //    "AND", URawData.Type, "= 0",
                //    "ORDER BY", URawData.Sequence, "DESC"
                //    );

                //    using (SqlCommand command = CreateCommand(connection, sql))
                //    {
                //        command.Parameters.AddRange(Parameters(new Input(sessionId, auditDatabaseId, targetTimeStamp)));
                //        Execute(command);
                //    }
                //}
            }
            else
            {
                throw new Exception("Unknown Audit Store type");
            }
        }
    }


    public class UnixSessionGetFrames
    {
        public enum Variables : int
        {
            [Variable(Direction.Input)]
            Session_Id,
            [Variable(Direction.Input)]
            AuditDatabaseId,
            [Variable(Direction.Input)]
            StartTimeStamp
        }

        public struct Input
        {
            [InputMapping((int)Variables.Session_Id)]
            public Guid SessionId;

            [InputMapping((int)Variables.AuditDatabaseId)]
            public int AuditDatabaseId;

            [InputMapping((int)Variables.StartTimeStamp)]
            public long StartTimeStamp;

            public Input(Guid sessionId, int auditDatabaseId, long startTimeStamp)
            {
                SessionId = sessionId;
                AuditDatabaseId = auditDatabaseId;
                StartTimeStamp = startTimeStamp;
            }
        }

        public struct Output
        {
            [ColumnMapping(typeof(RawData), (int)RawData.CapturedTime)]
            public DateTime CapturedTime;
            [ColumnMapping(typeof(RawData), (int)RawData.TimeStamp)]
            public long TimeStamp;
            [ColumnMapping(typeof(RawData), (int)RawData.Type)]
            public PacketType Type;
            [ColumnMapping(typeof(RawData), (int)RawData.TerminalData)]
            public byte[] Data;
            [ColumnMapping(typeof(RawData), (int)RawData.Sequence)]
            public int Sequence;
            [ColumnMapping(typeof(RawData), (int)RawData.ScreenHeight)]
            public int ScreenHeight;
            [ColumnMapping(typeof(RawData), (int)RawData.ScreenWidth)]
            public int ScreenWidth;
        }

        public static IEnumerable<Output> Execute(string connectionstring, Input input, CommandBehavior behavior)
        {
            //if (!CanReplayAuditTrail(sessionId, auditDatabaseId))
            //{
            //    RaiseUnauthorizedException("You have no permission to replay this Unix session");
            //}

            //try
            //{
            string connectionString = connectionstring;
            AuditStoreDatabaseType type = AuditStoreDatabaseType.WindowsAndUnix;

            string sql = string.Empty;

            if (type == AuditStoreDatabaseType.WindowsAndUnix)
            {
                sql = SqlHelper.Build(
                    // The snapshot and rawdata are in different tables.
                    // Here we get all the snapshots after @StartTimeStamp and all the frames after the first snapshot
                    "SELECT", Snapshot.CapturedTime, ",", Snapshot.TimeStamp, ",", 0, "AS [TYPE],", Snapshot.TerminalData, ",",
                    Snapshot.Sequence, "AS [Sequence], 0, 0",
                    "FROM", typeof(Snapshot), "WITH (NOLOCK)",
                    "WHERE", Snapshot.SessionId, "=", Variables.Session_Id,
                    "AND", Snapshot.TimeStamp, ">=", Variables.StartTimeStamp,
                    "UNION ALL",
                    "SELECT",
                 SqlHelper.List(RawData.CapturedTime, RawData.TimeStamp, RawData.Type, RawData.TerminalData, RawData.Sequence,
                         RawData.ScreenHeight, RawData.ScreenWidth),
                    "FROM", typeof(RawData), "WITH (NOLOCK)", "WHERE", RawData.SessionId, "=", Variables.Session_Id, "AND",
                    // We don't need to get the frames with the same timestamp of the first snapshot.  So, we do > @frame here
                    RawData.TimeStamp, ">", Variables.StartTimeStamp,
                    "ORDER BY [Sequence] ASC, [Type] DESC");

            }
            else if (type == AuditStoreDatabaseType.Unix)
            {
                sql = SqlHelper.Build(
                    "SELECT ",
                    URawData.CapturedTime, ",",
               SqlHelper.DateTimeToBigInt(URawData.CapturedTime), "AS [TimeStamp],",
                SqlHelper.List(URawData.Type, URawData.TerminalData, URawData.Sequence, URawData.ScreenHeight, URawData.ScreenWidth),
                    "FROM", typeof(URawData), "WITH (NOLOCK)",
                    "WHERE", URawData.Session_Id, "=", Variables.Session_Id,
                    // Snapshot and frames are both stored in rawdata table.
                    // Here we do >= because we know the timestamp of snapshot.
                    // We want to get the found snapshot and all the frames after the snapshot.
                    "AND", URawData.CapturedTime, ">=", SqlHelper.BigIntToDateTime(Variables.StartTimeStamp),
                    "ORDER BY", URawData.Sequence, "ASC,", URawData.Type, "DESC");
            }
            else
            {
                throw new Exception("Unknown Audit Store type");
            }
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    //command.Parameters.AddRange(Parameters(new Input(sessionId, auditDatabaseId, startTimeStamp)));
                    //Execute(command);

                    command.Parameters.AddWithValue("@Session_Id", input.SessionId);
                    command.Parameters.AddWithValue("@StartTimeStamp", input.StartTimeStamp);

                    SqlDataReader reader;
                    try
                    {
                        // Override command timeout
                        //if (commandTimeout >= 0)
                        //{
                        //    command.CommandTimeout = commandTimeout;
                        //}

                        reader = command.ExecuteReader(behavior);
                    }
                    catch (SqlException e)
                    {
                        //connection.Trace("Error - {0}", e.ToString());
                        throw;
                    }

                    int count = 0;
                    try
                    {
                        do
                        {
                            while (reader.Read())
                            {
                                count++;
                                //yield return CreateColumnOutput(reader);

                                Output output2 = new Output();
                                output2.CapturedTime = reader.GetDateTime(0);
                                output2.TimeStamp = reader.GetInt64(1);
                                output2.Type = (PacketType)((int)reader[2]);
                                output2.Data = reader.GetSqlBytes(3).Buffer;
                                output2.Sequence = reader.GetInt32(4);

                                output2.ScreenHeight = reader.IsDBNull(5) ? 0 : reader.GetInt32(5);
                                output2.ScreenWidth = reader.IsDBNull(6) ? 0 : reader.GetInt32(6);

                                yield return output2;
                            }
                        } while (reader.NextResult());
                    }
                    finally
                    {
                        command.Cancel();
                        reader.Close();
                    }


                }
            }
            //}
            //catch (SqlException e)
            //{
            //    //RaiseError(e);
            //}
        }
    }

    #endregion

    public static class SqlHelper
    {
        //public static readonly Settings Settings = new Settings();
        //public const string CONTEXT_CONNECTION = "context connection=true";

        /// <summary>
        /// Build the SQL statement by appending different elements in the object array
        /// The item inside the object array can be table, view, columns, variables or 
        /// static string
        /// </summary>
        /// <param name="values"></param>
        /// <returns></returns>
        public static string Build(params object[] values)
        {
            StringBuilder sql = new StringBuilder();
            foreach (object value in values)
            {
                // Table or view
                if (value is Type)
                {
                    string name = null;

                    TableAttribute table = Attribute.GetCustomAttribute((Type)value, typeof(TableAttribute)) as TableAttribute;
                    if (table != null) name = table.Name;

                    ViewAttribute view = Attribute.GetCustomAttribute((Type)value, typeof(ViewAttribute)) as ViewAttribute;
                    if (view != null) name = view.Name;

                    if (name == null) throw new Exception("The type should have either TableAttribute or ViewAttribute defined");
                    sql.Append(" [" + name + "] ");
                    continue;
                }

                // Normal static string
                if (value is string)
                {
                    sql.Append(" " + value + " ");
                    continue;
                }

                if (value is Enum)
                {
                    Type type = value.GetType();

                    // Variables
                    VariableAttribute variable = Attribute.GetCustomAttribute(type.GetField(value.ToString()), typeof(VariableAttribute)) as VariableAttribute;
                    if (variable != null)
                    {
                        sql.Append(" @" + value + " ");
                        continue;
                    }

                    // Full column name
                    ColumnAttribute column = Attribute.GetCustomAttribute(type.GetField(value.ToString()), typeof(ColumnAttribute)) as ColumnAttribute;
                    if (column != null)
                    {
                        string name = null;

                        TableAttribute table = Attribute.GetCustomAttribute((Type)type, typeof(TableAttribute)) as TableAttribute;
                        if (table != null) name = table.Name;

                        ViewAttribute view = Attribute.GetCustomAttribute((Type)type, typeof(ViewAttribute)) as ViewAttribute;
                        if (view != null) name = view.Name;

                        if (name != null) sql.Append(" [" + name + "].[" + value + "] ");
                        else sql.Append(" [" + value + "] ");
                        continue;
                    }

                    // Property name
                    PropertyAttribute property = Attribute.GetCustomAttribute(type.GetField(value.ToString()), typeof(PropertyAttribute)) as PropertyAttribute;
                    if (property != null)
                    {
                        sql.Append(" '" + value + "' ");
                        continue;
                    }

                    // Convert enum with various underlying type to corresponding numeric value
                    sql.Append(System.Convert.ChangeType(value, ((Enum)value).GetTypeCode()));
                    continue;
                }

                if (value is Guid)
                {
                    sql.Append(" '" + value.ToString() + "' ");
                    continue;
                }

                if (value is byte[])
                {
                    sql.Append(" " + BytesToHexString((byte[])value) + " ");
                    continue;
                }

                if (value is int)
                {
                    sql.Append(" " + value.ToString() + " ");
                    continue;
                }

                if (value is long)
                {
                    sql.Append(" " + value.ToString() + " ");
                    continue;
                }

                throw new Exception("Failed to build SQL statement"); // TODO: pass out the objects
            }

            return sql.ToString();
        }

        /// <summary>
        /// Compose the sql statement for CONVERT function
        /// toType can be SqlDataType or SystemPropertyItem enum
        /// expression can be variable
        /// </summary>
        /// <param name="toType"></param>
        /// <param name="expression"></param>
        /// <returns></returns>
        public static string Convert(object toType, object expression)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("CONVERT(");

            if (toType is Enum)
            {
                Type type = toType.GetType();

                // Property name
                PropertyAttribute property = Attribute.GetCustomAttribute(type.GetField(toType.ToString()), typeof(PropertyAttribute)) as PropertyAttribute;
                if (property != null)
                {
                    sql.Append(property.Type);

                    if (property.MaxLength.HasValue)
                    {
                        sql.AppendFormat("({0})", (property.MaxLength == -1) ? "MAX" : property.MaxLength.ToString());
                    }
                }
                else if (toType is SqlDbType)
                {
                    sql.Append(toType);
                }
                else
                {
                    throw new Exception("Failed to build SQL statement. Invalid type in Convert.");
                }
            }
            else
            {
                throw new Exception("Failed to build SQL statement. Invalid type in Convert.");
            }

            sql.Append(",");
            sql.Append(Build(expression));
            sql.Append(")");
            return sql.ToString();
        }

        /// <summary>
        /// Concatencate comma between each element of the item list.  The item inside the
        /// item lists can be table, view, columns, variables or static string
        /// </summary>
        /// <param name="items"></param>
        /// <returns></returns>
        public static string List(params object[] items)
        {
            List<string> names = new List<string>();
            foreach (object item in items)
            {
                names.Add(Build(item));
            }

            return string.Join(", ", names.ToArray());
        }

        /// <summary>
        /// Convert byte array to hex string
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public static string BytesToHexString(byte[] data)
        {
            StringBuilder sb = new StringBuilder("0x");

            foreach (byte aByte in data)
            {
                sb.Append(aByte.ToString("X2"));
            }

            return sb.ToString();
        }


        public static void GetValue<TValue>(this SqlCommand command, Enum variable, ref TValue value)
        {
            GetValue<TValue>(command, variable, ref value, true);
        }

        public static void GetValue<TValue>(this SqlCommand command, Enum variable, ref TValue value, bool writeUnmaskTraceValue)
        {
            SqlParameter param = command.Parameters["@" + variable.ToString()];

            if (param.Value is TValue)
            {
                value = (TValue)param.Value;
            }
            else if (param.SqlValue is TValue)
            {
                value = (TValue)param.SqlValue;
            }

            //command.Connection.Trace("{0} = {1}", "@" + variable.ToString(), (writeUnmaskTraceValue ? value : (object)SqlTrace.MASKED_TRACE_VALUE));
        }


        public static string BigIntToDateTime(object expression)
        {
            // We have some round off issue here.  In order to make sure the reverse operation is not smaller than the original one, we need to add 33.34 offset
            return string.Format(CultureInfo.InvariantCulture, "CAST((CAST({0} - 599266080000000000 AS FLOAT) + 33.34)/864000000000 AS DATETIME)",
                                 Build(expression));
        }
        public static string DateTimeToBigInt(object expression)
        {
            return string.Format(CultureInfo.InvariantCulture, "CAST(CAST({0} AS FLOAT)*864000000000 + 599266080000000000 AS BIGINT)",
                                 Build(expression));
        }

        public static string ToSql(params object[] values)
        {
            return SqlHelper.Build(values);
        }

        //protected static string AllColumns
        //{
        //    get
        //    {
        //        List<string> columns = new List<string>();
        //        foreach (FieldInfo fi in typeof(TOutput).GetFields(BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance))
        //        {
        //            ColumnMappingAttribute attr = Attribute.GetCustomAttribute(fi, typeof(ColumnMappingAttribute)) as ColumnMappingAttribute;
        //            if (attr != null)
        //            {
        //                columns.Add(attr.Name);
        //            }
        //        }

        //        return string.Join(", ", columns.ToArray());
        //    }
        //}
    }
}
